<?php
/**
 * @desc Excel 导入操作
 * @author Tinywan(ShaoBo Wan)
 * @email 756684177@qq.com
 * @date 2023/11/10 23:27
 */

namespace app\Http\Controllers\Admin;

use PhpOffice\PhpSpreadsheet\Exception;
use support\Request;
use support\Response;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
class Excel
{




    //添加表内容
    public function addData($data)
    {
        foreach ($data as $item) {
            $dataCol = 'A';
            foreach ($item as $value) {
                // 单元格内容写入
                $this->sheet->setCellValue($dataCol . $this->row, $value);
                $dataCol++;
            }
            $this->row++;
        }
        return $this;
    }


    /**
    $head = ['name'=>'名字','score'=>'得分'];
    $data = [
    ['name' => '张三', 'score' => '80'],
    ['name' => '李四', 'score' => '90'],
    ['name' => '王五', 'score' => '60'],
    ];
    $fileName = '测试'
     */
    /**
     * Describe:   导数数据 （csv 格式）
     * @param array $head
     * @param array $body
     * @param string $fileName '测试.csv','测试.xls'
     * @return \Psr\Http\Message\ResponseInterface
     * Created by lkz at 2021/11/8 14:47
     */
    static function csvExport(array $head, array $body, string $fileName)
    {
        $head_keys = array_keys($head);
        $head_values = array_values($head);
        $fileData = self::utfToGbk(implode(',', $head_values)) . "\n";

        foreach ($body as $value) {
            $temp_arr = [];
            foreach ($head_keys as $key) {
                $temp_arr[] = $value[$key] ?? '';
            }
            $fileData .= self::utfToGbk(implode(',', $temp_arr)) . "\n";
        }
        $response = new Response();
        $contentType = 'text/csv';
        return $response->withHeader('content-description', 'File Transfer')
            ->withHeader('content-type', $contentType)
            ->withHeader('content-disposition', "attachment; filename={$fileName}")
            ->withHeader('content-transfer-encoding', 'binary')
            ->withHeader('pragma', 'public')
            ->withBody($fileData);
    }

    /**
     * 字符转换（utf-8 => GBK）
     * @param $data
     * @return false|string
     */
    static function utfToGbk($data)
    {
        return mb_convert_encoding($data,"GBK","UTF-8");
        # return iconv('utf-8', 'GBK', $data);
    }



    // 利用xlsWriter快速导出数据,导出模板都可以使用
    static public function xlsxQuickExport($title, $data, $name = null)
    {
        // 判断是否为关联数组
        if (array_keys($title) !== range(0, count($title) - 1)) {
            // 关联数组要变为索引数组
            $title = array_values($title);
        }
        $config = ['path' => public_path().'/xls/']; // 设置文件保存路径
        $excel  = new \Vtiful\Kernel\Excel($config);
        $name = is_null($name)?uuid().'.xlsx':$name;
        $excel->fileName($name, 'Sheet1');
        $format  = new \Vtiful\Kernel\Format($excel->getHandle());
        $colorOneStyle = $format
            ->border(\Vtiful\Kernel\Format::BORDER_THIN)
            ->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER)
            ->toResource();
        $excel->defaultFormat($colorOneStyle)
            ->header($title) // 设置表首行名称
            ->defaultFormat($colorOneStyle)
            ->data($data)
            ->output();
        $fileName = public_path().'/xls/'.$name;
        $content = file_get_contents($fileName);
        unlink($fileName);
        $response = new Response();
        $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        return $response->withHeader('content-description', 'File Transfer')
            ->withHeader('content-type', $contentType)
            ->withHeader('content-disposition', "attachment; filename={$name}")
            ->withHeader('content-transfer-encoding', 'binary')
            ->withHeader('pragma', 'public')
            ->withBody($content);
    }

    // 导出excel表格
    static function xlsxExport($header, $Data, $name = null)
    {
        $name = is_null($name)?uuid().'.xlsx':$name;
        $spreadsheet = new Spreadsheet();
        $Sheet = $spreadsheet->getActiveSheet();
        $Sheet->getDefaultColumnDimension()->setWidth(10);
        $Sheet->getDefaultRowDimension()->setRowHeight(20);
        $HeaderCount=count($header);
        // 判断是否为关联数组
        if (array_keys($header) !== range(0, count($header) - 1)) {
            // 关联数组要变为索引数组
            $header = array_values($header);
        }
        // 加入边框
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' =>  \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN ,//细边框
                    'color' => ['argb' => 'FF000000']
                ],
            ],
        ];
        for($i=0;$i<$HeaderCount;$i++){
////            $cell =
//            $Sheet->setCellValue();
////            setCellValueByColumnAndRow($i+1,1,$header[$i]);
//            $Sheet->getStyleByColumnAndRow($i+1, 1)->applyFromArray($styleArray);
//            $Sheet->getStyleByColumnAndRow($i+1, 1)->getFont()->setBold(true)->setName('Arial')->setSize(10);
        }
        $RowIndex=2;
        $DataCount=count($Data);
        for($i=0;$i<$DataCount;$i++){
            $ColumnIndex=1;
            foreach ($Data[$i] as $Key=>$Value){
//                if(in_array($Key,$LongNumberField)){
//                    $Sheet->setCellValueExplicitByColumnAndRow($ColumnIndex,$RowIndex,$Value,\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
//                    $Sheet->getStyleByColumnAndRow($ColumnIndex, $RowIndex)->applyFromArray($styleArray);
//                }
//                else{
//                    $Sheet->setCellValueByColumnAndRow($ColumnIndex,$RowIndex,$Value);
//                    $Sheet->getStyleByColumnAndRow($ColumnIndex, $RowIndex)->applyFromArray($styleArray);
//                }
                $ColumnIndex++;
            }
            $RowIndex++;
        }
        $writer = new Xlsx($spreadsheet);
        $file = public_path().'/xls/'.$name;
        $writer->save($file);
        $content = file_get_contents($file);
        unlink($file);
        $response = new Response();
        $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        return $response->withHeader('content-description', 'File Transfer')
            ->withHeader('content-type', $contentType)
            ->withHeader('content-disposition', "attachment; filename={$name}")
            ->withHeader('content-transfer-encoding', 'binary')
            ->withHeader('pragma', 'public')
            ->withBody($content);
    }

    static public function readXlsxFile()
    {
        /**
         * 读取excel文件内容
         */
            $file = request()->file('file');
            var_dump($file);
//            $inputFileName = ;
//            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
//            // 方法二
//            $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
//            return $sheetData;

    }

    // 导入电子表格文件，返回为索引数组
    Static public function readXlsxArray()
    {

        $file = request()->file('file');
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file->getPathname());
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
        return $sheetData;
    }

    // 返回为关联数组
    Static public function readXlsxObject(){
        $file = request()->file('file');
        $excel = new \Rap2hpoutre\FastExcel\FastExcel();
        $collection = $excel->import($file->getPathname());
        $arrData = $collection->toArray();
        return $arrData;
    }

    static public function exportTemplate($data = [], $name = null)
    {
        $list = collect($data);
        // 直接下载
        $excel = new \Rap2hpoutre\FastExcel\FastExcel();
        $name = is_null($name)?uuid().".xlsx":'template.xlsx';
        return $excel->data($list)->download($name);
    }





}